#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def Monthly_Holding_Report(ref_date, portf_id):

	tplt_file = TPLT_PATH + 'Monthly Holding Report.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	xSht = xBook.Worksheets('Report')
	
	d = date(2014, 3 ,14)
	row = 3
	
	while d <= ref_date:
	
		hld = query_hld(d, portf_id)
		rcd = {}
		blp_tick = []
		
		for elem in hld:
			ticker = elem[0]
			sec_name = elem[1]			
			sec_type = elem[2]
			
			if sec_type == 'WAEEANT' or sec_type == 'CASH' or sec_type == 'REPO':
				continue
			
			amt = elem[4]
			exp = elem[14]
			price = elem[7]
			mkt_val = elem[9]
			local_curncy = elem[17]
			
			#blp ticke
			blp_ticker = ch_ticker_to_blp_ticker(ticker)
			blp_tick.append(blp_ticker)
				
			rcd[blp_ticker] = [ticker, sec_name, amt, exp, price, mkt_val, local_curncy]	
		
		if blp_tick:
			sec_info_dict = query_blp_static(blp_tick, ['NAME','ID_SEDOL1', 'ID_ISIN'])
		
		for tick in rcd.keys():
			ticker = rcd[tick][0]
			ch_name = rcd[tick][1]
			amt = rcd[tick][2] 
			exp = rcd[tick][3]
			price = rcd[tick][4]
			mkt_val = rcd[tick][5]
			local_curncy = rcd[tick][6]
			en_name = sec_info_dict[tick]['NAME']
			sedol = sec_info_dict[tick]['ID_SEDOL1']
			isin = sec_info_dict[tick]['ID_ISIN']
			
			#
			xSht.Cells(row, 1).Value = d
			xSht.Cells(row, 2).Value = ticker
			xSht.Cells(row, 3).Value = sedol
			xSht.Cells(row, 4).Value = ch_name
			xSht.Cells(row, 5).Value = en_name
			xSht.Cells(row, 6).Value = local_curncy
			xSht.Cells(row, 7).Value = amt
			xSht.Cells(row, 8).Value = price
			xSht.Cells(row, 9).Value = mkt_val
			xSht.Cells(row, 10).Value = query_first_bought_date(ticker, portf_id[0])
			xSht.Cells(row, 11).Value = exp
			xSht.Cells(row, 12).Value = isin
			
			row += 1
	
		print '   running info, holding completed at ', d.isoformat()
		d = end_of_mth(d, 1)
		
	rept_file = REPT_PATH + 'Monthly Holding Report.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp
	
	print 'info, monthly holding report completed'	
	
def query_first_bought_date(ticker, portf_id):

	sql_tpl = Template('''select min(trade_date) from transaction 
							where ticker = '${TICK}'
							and portfolio_id = '${PORTF_ID}' ''')
			
	sql_text = sql_tpl.substitute(TICK = ticker, PORTF_ID = portf_id)
	
	cursor = DBConnFactory().get_db_connection('PKEDB').cursor()
	cursor.execute(sql_text)
	return cursor.fetchall()[0][0]